package me.sdevil507.supports.excel;


import lombok.Data;
import me.sdevil507.biz.fruit.dto.ResultServicesHomeListFruitDTO;
import me.sdevil507.biz.fruit.po.*;
import me.sdevil507.supports.status.ComConstant;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: tips_back_end
 * @description:
 * @author: kzl
 * @create: 2020-07-03 14:39
 **/
@Data
public class ExportExcel {

    /**显示的导出表的标题**/
    private String title;
    //导出表的列名--常规列名
    private String[] rowsName;
    //导出表的列名--基础
    private List<String> rowsBasicName;
    //导出表的列名--集合
    private List<String> rowsListName;

    private List<Object[]> dataList = new ArrayList<Object[]>();

    HttpServletResponse response;

    public ExportExcel() {
    }

    //构造方法，传入要导出的数据
    public ExportExcel(String title, List<String> rowsBasicName, List<String> rowsListName, List<Object[]> dataList) {
        this.title = title;
        this.rowsBasicName = rowsBasicName;
        this.rowsListName = rowsListName;
        this.dataList = dataList;
    }

    // 导出数据--多个单元格
    public void export(HSSFWorkbook workbook, HSSFSheet sheet, int x) throws IOException {
        File tempFile = File.createTempFile("temp","test.xls");
        // 设置默认列宽，width为字符个数,8个字符换一次行
        sheet.setDefaultColumnWidth(16);
        sheet.setColumnWidth(0, 9 * 256);
        //名称
        sheet.setColumnWidth(1, 9 * 256 * 8);
        //自适应列宽度
//        sheet.autoSizeColumn(1);
//        sheet.autoSizeColumn(1, true);
        try {

            // 产生表格标题行
            int sheetRowNum = 0;
            HSSFRow rowm = sheet.createRow(sheetRowNum);
            HSSFCell cellTiltle = rowm.createCell(0);
            //设置高度
            rowm.setHeight((short) (25 * 35));

            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            //获取列头样式对象
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);
            //合并单元格的数量
            int mergeNum = rowsBasicName.size();

            //------------------------------- 作者信息-------------------------------
            int authorLocation = getIndex(rowsBasicName, "作者,与会专家,来访专家,采访专家,项目负责人");
            boolean isContainsAuthorInfo =  authorLocation > -1 ? true : false;

            //------------------------------- 报送信息-------------------------------
            String[] bsRowName = {"报送发部门","报送发级别","报送发时间","途径部门","采用部门","采用时间","采用刊物","备注"};
            int bsLocation = getIndex(rowsListName, "报送信息");
            boolean isContainsBsInfo =  bsLocation > -1 ? true : false;
            if(isContainsBsInfo){
                mergeNum += bsRowName.length;
            }
            //------------------------------- 批示信息-------------------------------
            String[] psRowName = {"批示领导姓名","单位","职务","级别","批示类型","报送渠道","批示时间"};
            int psLocation =  getIndex(rowsListName, "批示信息");
            boolean isContainsPsInfo = psLocation > -1 ? true : false;
            if(isContainsPsInfo){
                mergeNum += psRowName.length;
            }

            //合并大标题行
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, mergeNum));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            // 定义所需列数
            int columnNum = mergeNum+1;
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            sheetRowNum += 1;
            HSSFRow rowRowName = sheet.createRow(sheetRowNum);
            //设置高度
            rowRowName.setHeight((short) (25 * 25));

            // 将列头设置到sheet的单元格中
            for (int n = 0; n < rowsBasicName.size()+1; n++) {
                //创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                HSSFRichTextString text;
                if(n==0){
                    text = new HSSFRichTextString("序号");
                }else {
                    text = new HSSFRichTextString(rowsBasicName.get(n-1));
                }
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
            }
            //报送列的开始结束位置
            int bsStartLocation = 0;
            int bsEndLocation = 0;
            //批示列的开始结束位置
            int psStartLocation = 0;
            int psEndLocation = 0;
            //如果有报送  设置第三行的抬头字段
            if(isContainsBsInfo || isContainsPsInfo){
                sheetRowNum += 1;

                //创建第三行
                HSSFRow threeRowName = sheet.createRow(sheetRowNum);
                //设置高度
                threeRowName.setHeight((short) (25 * 25));
                //报送行
                if(isContainsBsInfo){
                    bsStartLocation = rowRowName.getLastCellNum();
                    bsEndLocation = rowRowName.getLastCellNum() + bsRowName.length - 1;
                    //创建合并的标题
                    HSSFCell bsTiltleCell = null;
                    for (int i = 0; i < bsRowName.length; i++) {
                        //循环创建 为了getLastCellNum能获取最后一个单元格的列数
                        bsTiltleCell = rowRowName.createCell(bsStartLocation + i);
                        bsTiltleCell.setCellStyle(columnTopStyle);
                        bsTiltleCell.setCellValue("报送信息");
                        //创建列头对应个数的单元格
                        HSSFCell twoRowCellName = threeRowName.createCell(bsStartLocation + i);
                        //设置列头单元格的数据类型
                        twoRowCellName.setCellType(CellType.STRING);
                        HSSFRichTextString text = new HSSFRichTextString(bsRowName[i]);
                        //设置列头单元格的值
                        twoRowCellName.setCellValue(text);
                        //设置列头单元格样式
                        twoRowCellName.setCellStyle(columnTopStyle);
                    }
                    //合并报送信息标题
                    sheet.addMergedRegion(new CellRangeAddress(sheetRowNum - 1, sheetRowNum - 1, bsStartLocation, bsEndLocation));

                }
                //如果有批示  设置第三行的数据抬头字段
                if(isContainsPsInfo){
                    psStartLocation = rowRowName.getLastCellNum();
                    psEndLocation = rowRowName.getLastCellNum() + psRowName.length - 1;
                    //创建合并信息
                    HSSFCell psTiltleCell = null;
                    for (int i = 0; i < psRowName.length; i++) {
                        //循环创建 为了getLastCellNum能获取最后一个单元格的列数
                        psTiltleCell = rowRowName.createCell(psStartLocation + i);
                        psTiltleCell.setCellStyle(columnTopStyle);
                        psTiltleCell.setCellValue("批示信息");
                        //创建列头对应个数的单元格
                        HSSFCell threeRowCellName = threeRowName.createCell(psStartLocation + i);
                        //设置列头单元格的数据类型
                        threeRowCellName.setCellType(CellType.STRING);
                        HSSFRichTextString text = new HSSFRichTextString(psRowName[i]);
                        //设置列头单元格的值
                        threeRowCellName.setCellValue(text);
                        //设置列头单元格样式
                        threeRowCellName.setCellStyle(columnTopStyle);
                    }
                    //合并批示信息
                    sheet.addMergedRegion(new CellRangeAddress(sheetRowNum - 1, sheetRowNum - 1, psStartLocation, psEndLocation));
                }
                //合并其余信息  两行并一行
                if(isContainsBsInfo || isContainsPsInfo){
                    for (int basIndex = 0; basIndex < rowsBasicName.size()+1; basIndex++) {
                        //设置第三行 空单元格格式
                        HSSFCell threeRowCell = threeRowName.createCell(basIndex);
                        threeRowCell.setCellStyle(columnTopStyle);
                        threeRowCell.setCellValue("");
                        sheet.addMergedRegion(new CellRangeAddress(sheetRowNum - 1, sheetRowNum, basIndex, basIndex));
                    }
                }

            }

            //一条数据最大行数--因有集合数据
            int oneRowMaxNum = 0;
            //设置数据 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                //遍历每个对象
                Object[] obj = dataList.get(i);
                //如果选择了作者
                List<FruitAuthorPO> authorPOList = new ArrayList<>();
                //判断作者有多少行
                int authorNum = 0;
                if(isContainsAuthorInfo) {
                    //作者数据
                    authorPOList = (List<FruitAuthorPO>) obj[authorLocation];
                    //判断作者有多少行
                    authorNum = authorPOList.size();
                }
                //创建每条数据的第一行
                HSSFRow row = sheet.createRow(sheetRowNum + 1);
                //设置高度
                row.setHeight((short) (25 * 20));
                //----------------基础字段----------------
                for (int j = 0; j < rowsBasicName.size() + 1; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j, CellType.NUMERIC);
                    //设置单元格样式
                    cell.setCellStyle(style);
                    if (j == 0) {
                        cell.setCellValue(i + 1);
                    } else {
                        if(("作者").equals(rowsBasicName.get(j-1))||
                                ("与会专家").equals(rowsBasicName.get(j-1)) ||
                                ("采访专家").equals(rowsBasicName.get(j-1)) ||
                                ("来访专家").equals(rowsBasicName.get(j-1)) ||
                                ("项目负责人").equals(rowsBasicName.get(j-1))){
                            cell.setCellValue(!CollectionUtils.isEmpty(authorPOList)? authorPOList.get(0).getAuthor() : "");
                        }else if(("单位").equals(rowsBasicName.get(j-1))) {
                            //设置单位那一列的宽度
                            sheet.setColumnWidth(j, 9 * 256 * 6);
                            cell.setCellValue(
                                !CollectionUtils.isEmpty(authorPOList) ?
                                        authorPOList.get(0).getOrgPOList().stream()
                                                .map(FruitAuthorOrgPO::getName).collect(Collectors.joining(","))
                                        : ""
                            );
                        }else if(("专家职务").equals(rowsBasicName.get(j-1))){
                            cell.setCellValue(authorPOList.get(0).getJob());
                        }else if(("专家观点").equals(rowsBasicName.get(j-1))){
                            cell.setCellValue(authorPOList.get(0).getViewPoint());
                        }else {
                            if (!"".equals(obj[j - 1]) && obj[j - 1] != null) {
                                //设置单元格的值
                                cell.setCellValue(obj[j - 1].toString());
                            } else {
                                cell.setCellValue("");
                            }
                        }
                    }
                }

                //---------------集合数据——----------------------
                //报送一共多少行
                int bsListRowNum = 0;
                List<FruitSubPO> subList = new ArrayList<>();
                //批示一共多少行
                int psListRowNum = 0;
                List<FruitLeaderPO> leaderList = new ArrayList<>();
                if(isContainsBsInfo || isContainsPsInfo) {
                    //循环比较
                    for (int j = 0; j < rowsListName.size(); j++) {
                        //数据下标
                        int index = j + rowsBasicName.size();
                        if (("报送信息").equals(rowsListName.get(j))) {
                            subList = (List<FruitSubPO>) obj[index];
                            if (!(CollectionUtils.isEmpty(subList))) {
                                bsListRowNum = subList.size();
                            }
                        }
                        if (("批示信息").equals(rowsListName.get(j))) {
                            leaderList = (List<FruitLeaderPO>) obj[index];
                            if (!(CollectionUtils.isEmpty(leaderList))) {
                                psListRowNum = leaderList.size();
                            }
                        }
                    }
                }
                //比较集合，取最大的 用来创建行数  authorNum  bsListRowNum  psListRowNum
                oneRowMaxNum = bsListRowNum >= psListRowNum ? bsListRowNum >= authorNum ? bsListRowNum : authorNum : psListRowNum;
                if(oneRowMaxNum > 0){
                    if(oneRowMaxNum == 1){
                        //第一行不用生成行 基础字段已生成
                        //设置报送数据( "报送发部门","报送发级别","报送发时间","途径部门","采用部门","采用时间","采用刊物","备注" )
                        if(isContainsBsInfo) {
                            if (!CollectionUtils.isEmpty(subList)) {
                                FruitSubPO fruitSubPO = subList.get(0);
                                //设置单元格的数据类型
                                this.setFruitDataList(workbook, row, style, bsStartLocation, fruitSubPO, null, 1);
                            } else {
                                this.setFruitDataList(workbook, row, style, bsStartLocation, null, null, 1);
                            }
                        }
                        if(isContainsPsInfo){
                            //设置批示数据( "批示领导姓名","单位","职务","级别","批示类型","批示时间" )
                            if(!CollectionUtils.isEmpty(leaderList)){
                                FruitLeaderPO fruitLeaderPO = leaderList.get(0);
                                //设置单元格的数据类型
                                this.setFruitDataList(workbook, row, style, psStartLocation, null, fruitLeaderPO, 2);
                            }else{
                                this.setFruitDataList(workbook, row, style, psStartLocation, null, null, 2);
                            }
                        }
                    }else{
                        //循环生成行
                        for (int frn = 1; frn <= oneRowMaxNum; frn++) {
                            //集合行数起始行
                            HSSFRow listStartRow = null;
                            //第一行 就用基础行
                            if(frn == 1){
                                listStartRow = row;
                            }else{
                                listStartRow = sheet.createRow(frn + sheetRowNum);
                                //设置高度
//                                listStartRow.setHeight((short) (25 * 20));
                                //----------------基础字段----------------
                                for (int j = 1; j < rowsBasicName.size() + 1; j++) {
                                    //设置单元格的数据类型
                                    HSSFCell cell = listStartRow.createCell(j, CellType.NUMERIC);
                                    //设置单元格样式
                                    cell.setCellStyle(style);
                                    //如果包含作者 循环出剩下所有基础数据的行 并填充空单元格
                                    if(isContainsAuthorInfo) {
                                        //获取作者列表数据--不能越界
                                        if(frn <= authorNum){
                                            FruitAuthorPO fruitAuthorPO = authorPOList.get(frn-1);
                                            if(("作者").equals(rowsBasicName.get(j-1)) ||
                                                    ("与会专家").equals(rowsBasicName.get(j-1)) ||
                                                    ("采访专家").equals(rowsBasicName.get(j-1)) ||
                                                    ("来访专家").equals(rowsBasicName.get(j-1)) ||
                                                    ("项目负责人").equals(rowsBasicName.get(j-1))){
                                                cell.setCellValue(fruitAuthorPO.getAuthor());
                                            }else if(("单位").equals(rowsBasicName.get(j-1))){
                                                cell.setCellValue(
                                                        fruitAuthorPO.getOrgPOList().stream().map(FruitAuthorOrgPO::getName).collect(Collectors.joining(","))
                                                );
                                            }else if(("专家职务").equals(rowsBasicName.get(j-1))){
                                                cell.setCellValue(fruitAuthorPO.getJob());
                                            }else if(("专家观点").equals(rowsBasicName.get(j-1))){
                                                cell.setCellValue(fruitAuthorPO.getViewPoint());
                                            }else{
                                                cell.setCellValue("");
                                            }
                                        }else{
                                            cell.setCellValue("");
                                        }
                                    }else{
                                        cell.setCellValue("");
                                    }
                                }
                            }

                            //设置报送数据( "报送发部门","报送发级别","报送发时间","途径部门","采用部门","采用时间","采用刊物","备注" )
                            if(isContainsBsInfo) {
                                if(!CollectionUtils.isEmpty(subList) && subList.size() >= frn){
                                    FruitSubPO fruitSubPO = subList.get(frn - 1);
                                    //设置单元格的数据类型
                                    this.setFruitDataList(workbook, listStartRow, style, bsStartLocation, fruitSubPO, null,1);
                                }else{
                                    this.setFruitDataList(workbook, listStartRow, style, bsStartLocation, null, null,1);
                                }
                            }
                            //设置批示数据( "批示领导姓名","单位","职务","级别","批示类型","批示时间" )
                            if(isContainsPsInfo){
                                if(!CollectionUtils.isEmpty(leaderList) && leaderList.size() >= frn){
                                    FruitLeaderPO fruitLeaderPO = leaderList.get(frn - 1);
                                    //设置单元格的数据类型
                                    this.setFruitDataList(workbook, listStartRow, style, psStartLocation,null, fruitLeaderPO, 2);
                                }else{
                                    this.setFruitDataList(workbook, listStartRow, style, psStartLocation,null, null, 2);
                                }
                            }
                        }
                        //合并基础数据行
                        for (int basIndex = 0; basIndex < rowsBasicName.size() + 1; basIndex++) {
                            if(basIndex > 0){
                                if(!("作者").equals(rowsBasicName.get(basIndex-1)) && !("单位").equals(rowsBasicName.get(basIndex-1)) &&
                                    !("项目负责人").equals(rowsBasicName.get(basIndex-1)) && !("来访专家").equals(rowsBasicName.get(basIndex-1)) &&
                                        !("与会专家").equals(rowsBasicName.get(basIndex-1)) && !("采访专家").equals(rowsBasicName.get(basIndex-1)) &&
                                        !("专家职务").equals(rowsBasicName.get(basIndex-1)) && !("专家观点").equals(rowsBasicName.get(basIndex-1))){
                                    sheet.addMergedRegion(new CellRangeAddress(sheetRowNum + 1, sheetRowNum + oneRowMaxNum, basIndex, basIndex));
                                }
                            }else {
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowNum + 1, sheetRowNum + oneRowMaxNum, basIndex, basIndex));
                            }
                        }
                    }
                }
                //设置下一条数据起始行--默认有一行
                sheetRowNum += oneRowMaxNum == 0 ? 1 : oneRowMaxNum;
           }

            //让列宽随着导出的列长自动适应
//            for (int colNum = 0; colNum < columnNum; colNum++) {
//                int columnWidth = sheet.getColumnWidth(colNum) / 256;
//                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
//                    HSSFRow currentRow;
//                    //当前行未被使用过
//                    if (sheet.getRow(rowNum) == null) {
//                        currentRow = sheet.createRow(rowNum);
//                    } else {
//                        currentRow = sheet.getRow(rowNum);
//                    }
//                    if (currentRow.getCell(colNum) != null) {
//                        HSSFCell currentCell = currentRow.getCell(colNum);
//                        if (currentCell.getCellType() == CellType.STRING) {
////                            System.out.println("----------："+currentCell.getStringCellValue());
//                            int length = currentCell.getStringCellValue().getBytes().length;
//                            if (columnWidth < length) {
//                                columnWidth = length;
//                            }
//                        }
//                    }
//                }
//                if (colNum == 0) {
//                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 128);
//                } else {
//                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
//                }
//            }

            if (workbook != null && x == 0) {
                try {
                    FileOutputStream out = new FileOutputStream(tempFile);
                    workbook.write(out);
                    out.close();
                    tempFile.delete();

                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 导出数据--一个单元格
    public void exportDataInOneCell(HSSFWorkbook workbook, HSSFSheet sheet, int x)throws IOException {
        File tempFile = File.createTempFile("temp","test.xls");
        // 设置默认列宽，width为字符个数,8个字符换一次行
        sheet.setDefaultColumnWidth(16);
        sheet.setColumnWidth(0, 9 * 256);
        //名称
        sheet.setColumnWidth(1, 9 * 256 * 8);
        //自适应列宽度
//        sheet.autoSizeColumn(1);
//        sheet.autoSizeColumn(1, true);
        try {

            // 产生表格标题行
            int sheetRowNum = 0;
            HSSFRow rowm = sheet.createRow(sheetRowNum);
            HSSFCell cellTiltle = rowm.createCell(0);
            //设置高度
            rowm.setHeight((short) (25 * 35));

            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            //获取列头样式对象
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);
            //合并单元格的数量
            int mergeNum = rowsBasicName.size();

            //------------------------------- 作者信息-------------------------------
            int authorLocation = getIndex(rowsBasicName, "作者,与会专家,采访专家,来访专家,项目负责人");
            boolean isContainsAuthorInfo =  authorLocation > -1 ? true : false;

            //------------------------------- 报送信息-------------------------------
            String[] bsRowName = {"报送发部门","报送发级别","报送发时间","途径部门","采用部门","采用时间","采用刊物","备注"};
            int bsLocation = getIndex(rowsListName, "报送信息");
            boolean isContainsBsInfo =  bsLocation > -1 ? true : false;
            if(isContainsBsInfo){
                mergeNum += bsRowName.length;
            }
            //------------------------------- 批示信息-------------------------------
            String[] psRowName = {"批示领导姓名","单位","职务","级别","批示类型","报送渠道","批示时间"};
            int psLocation =  getIndex(rowsListName, "批示信息");
            boolean isContainsPsInfo = psLocation > -1 ? true : false;
            if(isContainsPsInfo){
                mergeNum += psRowName.length;
            }

            //合并大标题行
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, mergeNum));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            // 定义所需列数
            int columnNum = mergeNum+1;
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            sheetRowNum += 1;
            HSSFRow rowRowName = sheet.createRow(sheetRowNum);
            //设置高度
            rowRowName.setHeight((short) (25 * 25));

            // 将列头设置到sheet的单元格中
            for (int n = 0; n < rowsBasicName.size()+1; n++) {
                //创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                HSSFRichTextString text;
                if(n==0){
                    text = new HSSFRichTextString("序号");
                }else {
                    text = new HSSFRichTextString(rowsBasicName.get(n-1));
                }
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
            }
            //报送列的开始结束位置
            int bsStartLocation = 0;
            int bsEndLocation = 0;
            //批示列的开始结束位置
            int psStartLocation = 0;
            int psEndLocation = 0;
            //如果有报送  设置第三行的抬头字段
            if(isContainsBsInfo || isContainsPsInfo){
                sheetRowNum += 1;

                //创建第三行
                HSSFRow threeRowName = sheet.createRow(sheetRowNum);
                //设置高度
                threeRowName.setHeight((short) (25 * 25));
                //报送行
                if(isContainsBsInfo){
                    bsStartLocation = rowRowName.getLastCellNum();
                    bsEndLocation = rowRowName.getLastCellNum() + bsRowName.length - 1;
                    //创建合并的标题
                    HSSFCell bsTiltleCell = null;
                    for (int i = 0; i < bsRowName.length; i++) {
                        //循环创建 为了getLastCellNum能获取最后一个单元格的列数
                        bsTiltleCell = rowRowName.createCell(bsStartLocation + i);
                        bsTiltleCell.setCellStyle(columnTopStyle);
                        bsTiltleCell.setCellValue("报送信息");
                        //创建列头对应个数的单元格
                        HSSFCell twoRowCellName = threeRowName.createCell(bsStartLocation + i);
                        //设置列头单元格的数据类型
                        twoRowCellName.setCellType(CellType.STRING);
                        HSSFRichTextString text = new HSSFRichTextString(bsRowName[i]);
                        //设置列头单元格的值
                        twoRowCellName.setCellValue(text);
                        //设置列头单元格样式
                        twoRowCellName.setCellStyle(columnTopStyle);
                    }
                    //合并报送信息标题
                    sheet.addMergedRegion(new CellRangeAddress(sheetRowNum - 1, sheetRowNum - 1, bsStartLocation, bsEndLocation));

                }
                //如果有批示  设置第三行的数据抬头字段
                if(isContainsPsInfo){
                    psStartLocation = rowRowName.getLastCellNum();
                    psEndLocation = rowRowName.getLastCellNum() + psRowName.length - 1;
                    //创建合并信息
                    HSSFCell psTiltleCell = null;
                    for (int i = 0; i < psRowName.length; i++) {
                        //循环创建 为了getLastCellNum能获取最后一个单元格的列数
                        psTiltleCell = rowRowName.createCell(psStartLocation + i);
                        psTiltleCell.setCellStyle(columnTopStyle);
                        psTiltleCell.setCellValue("批示信息");
                        //创建列头对应个数的单元格
                        HSSFCell threeRowCellName = threeRowName.createCell(psStartLocation + i);
                        //设置列头单元格的数据类型
                        threeRowCellName.setCellType(CellType.STRING);
                        HSSFRichTextString text = new HSSFRichTextString(psRowName[i]);
                        //设置列头单元格的值
                        threeRowCellName.setCellValue(text);
                        //设置列头单元格样式
                        threeRowCellName.setCellStyle(columnTopStyle);
                    }
                    //合并批示信息
                    sheet.addMergedRegion(new CellRangeAddress(sheetRowNum - 1, sheetRowNum - 1, psStartLocation, psEndLocation));
                }
                //合并其余信息  两行并一行
                for (int basIndex = 0; basIndex < rowsBasicName.size()+1; basIndex++) {
                    //设置第三行 空单元格格式
                    HSSFCell threeRowCell = threeRowName.createCell(basIndex);
                    threeRowCell.setCellStyle(columnTopStyle);
                    threeRowCell.setCellValue("");
                    sheet.addMergedRegion(new CellRangeAddress(sheetRowNum - 1, sheetRowNum, basIndex, basIndex));
                }

            }

            //一条数据最大行数--因有集合数据
            int oneRowMaxNum = 0;
            //设置数据 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                //遍历每个对象
                Object[] obj = dataList.get(i);
                //如果选择了作者
                List<FruitAuthorPO> authorPOList = new ArrayList<>();
                //判断作者有多少行
//                int authorNum = 0;
                if(isContainsAuthorInfo) {
                    //作者数据
                    authorPOList = (List<FruitAuthorPO>) obj[authorLocation];
                    //判断作者有多少行
//                    authorNum = authorPOList.size();
                }
                //创建每条数据的第一行
                HSSFRow row = sheet.createRow(sheetRowNum + 1);
                //设置高度
                row.setHeight((short) (25 * 20));
                //----------------基础字段----------------
                for (int j = 0; j < rowsBasicName.size() + 1; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j, CellType.NUMERIC);
                    //设置单元格样式
                    cell.setCellStyle(style);
                    if (j == 0) {
                        cell.setCellValue(i + 1);
                    } else {
                        if(("作者").equals(rowsBasicName.get(j-1))|| ("与会专家").equals(rowsBasicName.get(j-1)) ||
                                ("采访专家").equals(rowsBasicName.get(j-1)) || ("来访专家").equals(rowsBasicName.get(j-1)) ||
                                ("项目负责人").equals(rowsBasicName.get(j-1))){
                            cell.setCellValue(
                                    !CollectionUtils.isEmpty(authorPOList) ?
                                            authorPOList.stream().map(FruitAuthorPO::getAuthor).collect(Collectors.joining(","))
                                            : ""
                            );
                        }else if(("单位").equals(rowsBasicName.get(j-1))) {
                            //设置高度
                            if(!CollectionUtils.isEmpty(authorPOList)){
                                row.setHeight((short) (25 * 35 * authorPOList.size() / 2));
                            }
                            //设置单位那一列的宽度
                            sheet.setColumnWidth(j, 9 * 256 * 6);
                            StringBuilder orgName = new StringBuilder();
                            if(!CollectionUtils.isEmpty(authorPOList)){
                                for (int n = 0; n < authorPOList.size(); n++) {
                                    String authOrgNames = authorPOList.get(n).getOrgPOList().stream().map(FruitAuthorOrgPO::getName).collect(Collectors.joining(","));
                                    orgName.append(authOrgNames);
                                    if(n != authorPOList.size()-1){
                                        orgName.append("\r\n");
                                    }
                                }
                                cell.setCellValue(orgName.toString());
                            }
                        }else if(("专家职务").equals(rowsBasicName.get(j-1))){
                            cell.setCellValue(
                                    !CollectionUtils.isEmpty(authorPOList) ?
                                            authorPOList.stream().map(FruitAuthorPO::getJob).collect(Collectors.joining(","))
                                            : ""
                            );
                        }else if(("专家观点").equals(rowsBasicName.get(j-1))){
                            cell.setCellValue(
                                    !CollectionUtils.isEmpty(authorPOList) ?
                                            authorPOList.stream().map(FruitAuthorPO::getViewPoint).collect(Collectors.joining(","))
                                            : ""
                            );
                        }else {
                            if (!"".equals(obj[j - 1]) && obj[j - 1] != null) {
                                //设置单元格的值
                                cell.setCellValue(obj[j - 1].toString());
                            } else {
                                cell.setCellValue("");
                            }
                        }
                    }
                }

                //---------------集合数据——----------------------
                List<FruitSubPO> subList = new ArrayList<>();
                List<FruitLeaderPO> leaderList = new ArrayList<>();
                if(isContainsBsInfo || isContainsPsInfo) {
                    //循环比较
                    for (int j = 0; j < rowsListName.size(); j++) {
                        //数据下标
                        int index = j + rowsBasicName.size();
                        if (("报送信息").equals(rowsListName.get(j))) {
                            subList = (List<FruitSubPO>) obj[index];
//                            if (!(CollectionUtils.isEmpty(subList))) {
//                                bsListRowNum = subList.size();
//                            }
                        }
                        if (("批示信息").equals(rowsListName.get(j))) {
                            leaderList = (List<FruitLeaderPO>) obj[index];
//                            if (!(CollectionUtils.isEmpty(leaderList))) {
//                                psListRowNum = leaderList.size();
//                            }
                        }
                    }
                }
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                //比较集合，取最大的 用来创建行数  authorNum  bsListRowNum  psListRowNum
                oneRowMaxNum = 1;
                //第一行不用生成行 基础字段已生成
                //设置报送数据( "报送发部门","报送发级别","报送发时间","途径部门","采用部门","采用时间","采用刊物","备注" )
                if(isContainsBsInfo) {
                    int bsCount = !CollectionUtils.isEmpty(subList) ? subList.size() : 0;
                    String showBsMsg = bsCount==1?"":"无";
                    //设置高度
                    if(!CollectionUtils.isEmpty(subList)){
                        int heightco = authorPOList.size() > subList.size() ? authorPOList.size() : subList.size();
                        row.setHeight((short) (25 * 35 * heightco / 2));
                    }
                    this.setRowCellValue( row, style, bsStartLocation,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getSubDeparment() != null ? fruitSubPO.getSubDeparment() : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 1,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getLevelName() != null ? fruitSubPO.getLevelName() : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 2,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getSubTime() != null ? sdf.format(fruitSubPO.getSubTime()): showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 3,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getWayDeparment() != null ? fruitSubPO.getWayDeparment() : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 4,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getAdoptDeparment() != null ? fruitSubPO.getAdoptDeparment() : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 5,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getAdoptTime() != null ? sdf.format(fruitSubPO.getAdoptTime()) : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 6,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getAdoptJournal() != null ? fruitSubPO.getAdoptJournal() : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, bsStartLocation + 7,
                            bsCount == 0 ? "" :
                                    subList.stream().map(fruitSubPO -> fruitSubPO.getRemark() != null ? fruitSubPO.getRemark() : showBsMsg).collect(Collectors.joining("\r\n"))
                    );
                }
                if(isContainsPsInfo){
                    int psCount = !CollectionUtils.isEmpty(leaderList) ? leaderList.size() : 0;
                    String showPsMsg = psCount==1?"":"无";
                    this.setRowCellValue( row, style, psStartLocation,
                            psCount == 0 ? "" :
                                leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getName() != null ? fruitLeaderPO.getName() : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, psStartLocation + 1,
                            psCount == 0 ? "" :
                                    leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getUnit() != null ? fruitLeaderPO.getUnit() : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, psStartLocation + 2,
                            psCount == 0 ? "" :
                                    leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getJob() != null ? fruitLeaderPO.getJob() : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, psStartLocation + 3,
                            psCount == 0 ? "" :
                                    leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getInclinationLevelName() != null ? fruitLeaderPO.getInclinationLevelName() : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, psStartLocation + 4,
                            psCount == 0 ? "" :
                                    leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getInclinationName() != null ? fruitLeaderPO.getInclinationName() : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, psStartLocation + 5,
                            psCount == 0 ? "" :
                                    leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getSubChannel() != null ? fruitLeaderPO.getSubChannel() : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                    this.setRowCellValue( row, style, psStartLocation + 6,
                            psCount == 0 ? "" :
                                    leaderList.stream().map(fruitLeaderPO -> fruitLeaderPO.getInclinationTime() != null ? sdf.format(fruitLeaderPO.getInclinationTime()) : showPsMsg).collect(Collectors.joining("\r\n"))
                    );
                }
                //设置下一条数据起始行--默认有一行
                sheetRowNum += oneRowMaxNum;
           }
            if (workbook != null && x == 0) {
                try {
                    FileOutputStream out = new FileOutputStream(tempFile);
                    workbook.write(out);
                    out.close();
                    tempFile.delete();

                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 返回字符串位置
     * @param arr
     * @param value
     * @return
     */
    public static int getIndex(List<String> arr, String value) {
        for (int i = 0; i < arr.size(); i++) {
            if (value.indexOf(arr.get(i)) > -1) {
                return i;
            }
        }
        return -1;
    }

    /**
     *
     * 设置单元格的数据类型 报送和批示数据
     *
     */
    public void setFruitDataList(HSSFWorkbook workbook, HSSFRow row, HSSFCellStyle style, int startLocation, FruitSubPO fruitSubPO, FruitLeaderPO fruitLeaderPO, int type) throws ParseException {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        if(type == 1){
            this.setRowCellValue( row, style, startLocation, fruitSubPO != null ? fruitSubPO.getSubDeparment(): "");
            this.setRowCellValue( row, style, startLocation + 1, fruitSubPO != null ? fruitSubPO.getLevelName(): "");
            this.setRowCellValue( row, style, startLocation + 2, fruitSubPO != null ? fruitSubPO.getSubTime() == null? "": format.format(fruitSubPO.getSubTime()): "");
            this.setRowCellValue( row, style, startLocation + 3, fruitSubPO != null ? fruitSubPO.getWayDeparment(): "");
            this.setRowCellValue( row, style, startLocation + 4, fruitSubPO != null ? fruitSubPO.getAdoptDeparment(): "");
            this.setRowCellValue( row, style, startLocation + 5, fruitSubPO != null ? fruitSubPO.getAdoptTime() == null ? "" : format.format(fruitSubPO.getAdoptTime()): "");
            this.setRowCellValue( row, style, startLocation + 6, fruitSubPO != null ? fruitSubPO.getAdoptJournal(): "");
            this.setRowCellValue( row, style, startLocation + 7, fruitSubPO != null ? fruitSubPO.getRemark(): "");
        }else if(type == 2){
            this.setRowCellValue( row, style, startLocation, fruitLeaderPO != null ? fruitLeaderPO.getName(): "");
            this.setRowCellValue( row, style, startLocation + 1, fruitLeaderPO != null ? fruitLeaderPO.getUnit(): "");
            this.setRowCellValue( row, style, startLocation + 2, fruitLeaderPO != null ? fruitLeaderPO.getJob(): "");
            this.setRowCellValue( row, style, startLocation + 3, fruitLeaderPO != null ? fruitLeaderPO.getInclinationLevelName(): "");
            this.setRowCellValue( row, style, startLocation + 4, fruitLeaderPO != null ? fruitLeaderPO.getInclinationName(): "");
            this.setRowCellValue( row, style, startLocation + 5, fruitLeaderPO != null ? fruitLeaderPO.getSubChannel(): "");
            this.setRowCellValue( row, style, startLocation + 6, fruitLeaderPO != null ? StringUtils.isEmpty(fruitLeaderPO.getInclinationTime())? "": format.format(fruitLeaderPO.getInclinationTime()) : "");
        }
    }

    public void setRowCellValue(HSSFRow row, HSSFCellStyle style, int location, String value){
        HSSFCell cell = row.createCell(location, CellType.NUMERIC);
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }

    /**
     * 列头单元格样式
     **/
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBold(true);
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return style;

    }

    /**
     * 列数据信息单元格样式
      **/
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }

    // 生成多个动态sheet，和sheet内部多个内容
    public void dynamicExport(HSSFWorkbook workbook, HSSFCellStyle columnTopStyle, HSSFCellStyle cellStyle, int sheetNum, String sheetTitle, List<ExportExcel> result)throws IOException {
        HSSFSheet sheet = workbook.createSheet();
        // 设置默认列宽，width为字符个数,10个字符换一次行
        sheet.setDefaultColumnWidth(20);
        sheet.setColumnWidth(0, 9 * 256);
        workbook.setSheetName(sheetNum, sheetTitle);

        File tempFile = File.createTempFile("temp","test.xls");
        //自适应列宽度
//        sheet.autoSizeColumn(1);
//        sheet.autoSizeColumn(1, true);
        //统计已经生成多少行
        int createdRowNum = 0;
        try {
            //循环生成表格
            if(!CollectionUtils.isEmpty(result)){
                for (int index = 0; index < result.size(); index++) {
                    String excelTitle = result.get(index).getTitle();
                    String[] headerRownames = result.get(index).getRowsName();
                    List<Object[]> dataResultList = result.get(index).getDataList();

                    // 产生表格标题行
                    HSSFRow rowm = sheet.createRow(createdRowNum);
                    HSSFCell cellTiltle = rowm.createCell(0);
                    //设置高度
                    rowm.setHeight((short) (25 * 35));
                    //合并单元格  起始行, 终止行, 起始列, 终止列
                    sheet.addMergedRegion(new CellRangeAddress(createdRowNum, createdRowNum, 0, (headerRownames.length-1)));

                    cellTiltle.setCellStyle(columnTopStyle);
                    cellTiltle.setCellValue(excelTitle);
                    //已生成行数+1
                    createdRowNum++;

                    // 定义所需列数
                    int columnNum = headerRownames.length;
                    // 在索引2的位置创建行(最顶端的行开始的第二行)
                    HSSFRow rowRowName = sheet.createRow(createdRowNum);
                    //设置高度
//                    rowRowName.setHeight((short) (25 * 25));
                    // 将列头设置到sheet的单元格中
                    for (int n = 0; n < columnNum; n++) {
                        //创建列头对应个数的单元格
                        HSSFCell cellRowName = rowRowName.createCell(n);
                        //设置列头单元格的数据类型
                        cellRowName.setCellType(CellType.STRING);
                        //设置列头单元格的值
                        cellRowName.setCellValue(new HSSFRichTextString(headerRownames[n]));
                        //设置列头单元格样式
                        cellRowName.setCellStyle(columnTopStyle);
                    }
                    //已生成行数+1
                    createdRowNum++;
                    //将查询出的数据设置到sheet对应的单元格中
                    for (int i = 0; i < dataResultList.size(); i++) {
                        //遍历每个对象
                        Object[] obj = dataResultList.get(i);
                        //创建所需的行数
                        HSSFRow row = sheet.createRow(createdRowNum + i);
                        //取出第二个列（内容最长）的那列
                        int tVal = obj[1].toString().length();
                        //商+余数
                        int quotient  = tVal/10;
                        int remainder = tVal%10 > 0 ? 1 : 0;
                        int res = quotient + remainder;
                        //设置高度
                        row.setHeight((short) (25 * 20 * res - res*100));
                        for (int j = 0; j < obj.length; j++) {
                            //设置单元格的数据类型
                            HSSFCell cell = row.createCell(j, CellType.STRING);
                            //设置单元格的值
                            cell.setCellValue(obj[j] != null ? obj[j]+"" : "");
                            //设置单元格样式
                            cell.setCellStyle(cellStyle);
                        }
                    }
                    //已生成行数+已生成表格行数-- 计算下一个表格起始行数-
                    createdRowNum +=dataResultList.size();
//                    //让列宽随着导出的列长自动适应
//                    for (int colNum = 0; colNum < columnNum; colNum++) {
//                        int columnWidth = sheet.getColumnWidth(colNum) / 256;
//                        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
//                            HSSFRow currentRow;
//                            //当前行未被使用过
//                            if (sheet.getRow(rowNum) == null) {
//                                currentRow = sheet.createRow(rowNum);
//                            } else {
//                                currentRow = sheet.getRow(rowNum);
//                            }
//                            if (currentRow.getCell(colNum) != null) {
//                                //取得当前的单元格
//                                HSSFCell currentCell = currentRow.getCell(colNum);
//                                int length = 0;
//                                //如果当前单元格类型为字符串
//                                if (currentCell.getCellType() == CellType.STRING) {
//                                    try {
//                                        length = currentCell.getStringCellValue().getBytes().length;
//                                    } catch (Exception e) {
//                                        e.printStackTrace();
//                                    }
//                                    if (columnWidth < length) {
//                                        //将单元格里面值大小作为列宽度
//                                        columnWidth = length;
//                                    }
//                                }
//                            }
//                        }
//                        if (colNum == 0) {
//                            sheet.setColumnWidth(colNum, (columnWidth - 2) * 128);
//                        } else {
//                            sheet.setColumnWidth(colNum, columnWidth * 256);
//                        }
//                    }
                    //已生成行数+1--新的表格和上面的表格空一行
                    createdRowNum++;
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 一个excel 生成sheet
    public void excelExport(HSSFWorkbook workbook, HSSFCellStyle columnTopStyle, HSSFCellStyle cellStyle, int sheetNum, String sheetTitle, ExportExcel result)throws IOException {
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);

        File tempFile = File.createTempFile("temp","test.xls");
        // 设置默认列宽，width为字符个数,10个字符换一次行
        sheet.setDefaultColumnWidth(16);
        sheet.setColumnWidth(0, 9 * 256);
        sheet.setColumnWidth(1, 9 * 256 * 8);
        //自适应列宽度
//        sheet.autoSizeColumn(1);
//        sheet.autoSizeColumn(1, true);
        //统计已经生成多少行
        int createdRowNum = 0;
        try {
            String excelTitle = result.getTitle();
            String[] headerRownames = result.getRowsName();
            List<Object[]> dataResultList = result.getDataList();

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(createdRowNum);
            HSSFCell cellTiltle = rowm.createCell(0);
            //设置高度
            rowm.setHeight((short) (25 * 35));
            //合并单元格  起始行, 终止行, 起始列, 终止列
            sheet.addMergedRegion(new CellRangeAddress(createdRowNum, createdRowNum, 0, (headerRownames.length-1)));

            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(excelTitle);
            //已生成行数+1
            createdRowNum++;

            // 定义所需列数
            int columnNum = headerRownames.length;
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(createdRowNum);
            //设置高度
            rowRowName.setHeight((short) (25 * 25));
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                //创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                //设置列头单元格的值
                cellRowName.setCellValue(new HSSFRichTextString(headerRownames[n]));
                //设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
            }
            //已生成行数+1
            createdRowNum++;
            //将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataResultList.size(); i++) {
                //遍历每个对象
                Object[] obj = dataResultList.get(i);
                //创建所需的行数
                HSSFRow row = sheet.createRow(createdRowNum + i);
                //设置高度
                row.setHeight((short) (25 * 20));
                for (int j = 0; j < obj.length; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j, CellType.STRING);
                    //设置单元格的值
                    cell.setCellValue(obj[j] != null ? obj[j]+"" : "");
                    //设置单元格样式
                    cell.setCellStyle(cellStyle);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void setCellValue(HSSFRow row, HSSFCellStyle style, int columnIndex, String value){
        //设置单元格的数据类型
        HSSFCell cell =  row.createCell(columnIndex, CellType.STRING);
        //设置单元格的值
        cell.setCellValue(value);
        //设置单元格样式
        cell.setCellStyle(style);

    }

    public static String getCellValue(Cell cell){
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case 0: // 数字
                //short s = cell.getCellStyle().getDataFormat();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
//                    SimpleDateFormat sdf = null;
                    // 验证short值
//                    if (cell.getCellStyle().getDataFormat() == 14) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
//                    } else if (cell.getCellStyle().getDataFormat() == 21) {
//                        sdf = new SimpleDateFormat("HH:mm:ss");
//                    } else if (cell.getCellStyle().getDataFormat() == 22) {
//                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
//                    } else {
//                        throw new RuntimeException("日期格式错误!!!");
//                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                } else if(cell.getCellStyle().getDataFormat() == 58) {
                    Date d = cell.getDateCellValue();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = formatter.format(d);
                }else{
                    // 取得当前Cell的数值
                    cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case 1: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case 2: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case 3: // 空值
                cellValue = null;
                break;
            case 4: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case 5: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public void exportSpecialCnPsData(HSSFWorkbook workbook, HSSFSheet sheet, String[] headerRowsName, List<ResultServicesHomeListFruitDTO> dataList) throws IOException {
        File tempFile = File.createTempFile("temp","test.xls");
        // 设置默认列宽，width为字符个数,8个字符换一次行
        sheet.setDefaultColumnWidth(16);
        sheet.setColumnWidth(0, 9 * 256);
        sheet.setColumnWidth(1, 9 * 256 * 5);
        sheet.setColumnWidth(2, 9 * 256 * 2);
        sheet.setColumnWidth(4, 9 * 256 * 5);
        sheet.setColumnWidth(5, 9 * 256 * 5);
        sheet.setColumnWidth(6, 9 * 256 * 5);
        // 产生表格行
        int sheetRowNum = 0;
        try {
            // 产生表格标题行
            HSSFRow titleRowm = sheet.createRow(sheetRowNum);
            //设置高度
            titleRowm.setHeight((short) (25 * 35));
            //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            //获取列头样式对象
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            //单元格样式对象
            HSSFCellStyle style = this.getStyle(workbook);

            // 将列头设置到sheet的单元格中
            for (int n = 0; n < headerRowsName.length; n++) {
                //创建列头对应个数的单元格
                HSSFCell cellRowName = titleRowm.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                HSSFRichTextString text = new HSSFRichTextString(headerRowsName[n]);
                //设置列头单元格的值
                cellRowName.setCellValue(text);
                //设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            //已生成行数+1
            sheetRowNum++;
            //设置数据 将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                //创建所需的行数
                HSSFRow row = sheet.createRow(sheetRowNum + i);
                //设置高度
                row.setHeight((short) (25 * 20));
                //遍历每个对象
                ResultServicesHomeListFruitDTO rshf = dataList.get(i);
                //采纳内容
                String cnContent = "";
                for (FruitSubPO fruitSubPO : rshf.getFruitSubPOList()) {
                    if((ComConstant.specialDeptName.JSSWXCB).equals(fruitSubPO.getSubDeparment()) && fruitSubPO.getSubDeptType() != null && fruitSubPO.getSubDeptType() == 1 ){
                        cnContent = sdf.format(fruitSubPO.getAdoptTime()) + "被" + fruitSubPO.getAdoptJournal() + "采纳";
                        break;
                    }
                }
                //批示内容
                String psContent = "";
                for (FruitLeaderPO fruitLeaderPO : rshf.getFruitLeaderList()) {
                    psContent += sdf.format(fruitLeaderPO.getInclinationTime()) + "被" + fruitLeaderPO.getInclinationLevelName() + "领导" + fruitLeaderPO.getName() + "批示\n";
                }
                if(!("").equals(psContent)){
                    psContent = psContent.substring(0, psContent.length()-1);
                }
                for (int j = 0; j < headerRowsName.length; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j, CellType.STRING);
                    //设置单元格样式
                    cell.setCellStyle(style);
                    if (j == 0) {
                        cell.setCellValue((i + 1)+"");
                    }else if (j == 1) {
                        cell.setCellValue(rshf.getName());
                    }else if (j == 2) {
                        cell.setCellValue(rshf.getAuthors());
                    }else if (j == 3) {
                        cell.setCellValue(sdf.format(rshf.getPubTime()));
                    }else if (j == 4) {
                        cell.setCellValue(rshf.getOrgName());
                    }else if (j == 5) {
                        cell.setCellValue(cnContent);
                    }else if (j == 6) {
                        cell.setCellValue(psContent);
                    }
                }
            }

            if (workbook != null) {
                try {
                    FileOutputStream out = new FileOutputStream(tempFile);
                    workbook.write(out);
                    out.close();
                    tempFile.delete();

                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {

        String psContent = "批示\n";
//        psContent = psContent.substring(0, psContent.length()-1);
        System.out.println(psContent + "------");
    }
}

